from app.db import db
from app.Models.Goal.GoalModel import Goal  # 导入 Goal 模型
from app.Models.Goal.GoalCateModel import GoalCate  # 一级分类模型
from app.Models.AdminModels.UserModels.UserModel import User  # 用户模型
from sqlalchemy import or_
from datetime import date


class GoalDao:
    """目标表 DAO 层，封装增删改查操作（含双外键名称关联查询）"""

    @staticmethod
    def add_goal(goal_data):
        """
        新增目标
        :param goal_data: 目标数据字典，需包含：user_id、first_cate_id、goal_name、start_date、end_date、goal_status
        :return: 新增的目标对象（含 id）
        """
        try:
            # 构建目标对象
            new_goal = Goal(
                user_id=goal_data.get("user_id"),  # 允许 user_id 为 None（如果是公共目标）
                first_cate_id=goal_data["first_cate_id"],
                goal_name=goal_data["goal_name"],
                start_date=goal_data["start_date"],  # 支持 date 类型或 YYYY-MM-DD 字符串
                end_date=goal_data["end_date"],
                goal_status=goal_data["goal_status"]
            )
            # 提交到数据库
            db.session.add(new_goal)
            db.session.commit()
            return new_goal
        except Exception as e:
            db.session.rollback()
            raise Exception(f"新增目标失败：{str(e)}")

    @staticmethod
    def delete_goal(goal_id):
        """
        根据 ID 删除目标
        :param goal_id: 目标 ID
        :return: bool：删除成功返回 True，失败返回 False
        """
        try:
            goal = Goal.query.get(goal_id)
            if not goal:
                raise Exception(f"目标 ID {goal_id} 不存在")
            db.session.delete(goal)
            db.session.commit()
            return True
        except Exception as e:
            db.session.rollback()
            raise Exception(f"删除目标失败：{str(e)}")

    @staticmethod
    def update_goal(goal_id, update_data):
        """
        根据 ID 更新目标
        :param goal_id: 目标 ID
        :param update_data: 待更新数据字典（可选字段：user_id、first_cate_id、goal_name、start_date、end_date、goal_status）
        :return: 更新后的目标对象
        """
        try:
            goal = Goal.query.get(goal_id)
            if not goal:
                raise Exception(f"目标 ID {goal_id} 不存在")

            # 批量更新字段（只更新传入的键，排除 id）
            for key, value in update_data.items():
                if hasattr(goal, key) and key != "id":
                    setattr(goal, key, value)

            db.session.commit()
            return goal
        except Exception as e:
            db.session.rollback()
            raise Exception(f"更新目标失败：{str(e)}")

    @staticmethod
    def get_goal_by_id(goal_id):
        """
        根据 ID 查询目标（关联查询用户名称 + 一级分类名称）
        :param goal_id: 目标 ID
        :return: 字典：包含目标所有字段 + username（用户名） + first_cate_name（分类名称）
        """
        try:
            # 关联查询：Goal 左关联 User 和 GoalCate（双外键关联）
            query_result = db.session.query(
                Goal,
                User.username,  # 关联查询用户名
                GoalCate.first_cate_name  # 关联查询分类名称
            ).join(
                User,
                Goal.user_id == User.id,
                isouter=True  # 左关联：允许 user_id 为 None（无归属用户）
            ).join(
                GoalCate,
                Goal.first_cate_id == GoalCate.id,
                isouter=True  # 左关联：允许分类不存在
            ).filter(
                Goal.id == goal_id
            ).first()

            if not query_result:
                return None  # 目标不存在

            goal, username, first_cate_name = query_result
            # 格式化返回结果（日期转字符串，外键名称补默认值）
            return {
                "id": goal.id,
                "user_id": goal.user_id,
                "username": username or "未分配用户",  # 无用户时默认提示
                "first_cate_id": goal.first_cate_id,
                "first_cate_name": first_cate_name or "未知分类",  # 无分类时默认提示
                "goal_name": goal.goal_name,
                "start_date": goal.start_date.strftime("%Y-%m-%d") if goal.start_date else None,
                "end_date": goal.end_date.strftime("%Y-%m-%d") if goal.end_date else None,
                "goal_status": goal.goal_status,
                "goal_status_desc": GoalDao._get_goal_status_desc(goal.goal_status)  # 状态描述（辅助字段）
            }
        except Exception as e:
            raise Exception(f"查询目标失败：{str(e)}")

    @staticmethod
    def query_goals(filters=None, page=1, per_page=10):
        """
        分页查询目标列表（支持多条件过滤，关联双外键名称）
        :param filters: 过滤条件字典（可选），支持：
            - user_id: 用户 ID
            - first_cate_id: 一级分类 ID
            - goal_status: 目标状态（0-未完成，1-完成，2-暂缓）
            - goal_name: 目标名称（模糊查询）
            - start_date_start: 开始日期起始（>=）
            - start_date_end: 开始日期结束（<=）
            - end_date_start: 结束日期起始（>=）
            - end_date_end: 结束日期结束（<=）
            - username: 用户名（模糊查询，需关联 User）
        :param page: 页码（默认第 1 页）
        :param per_page: 每页条数（默认 10 条）
        :return: 分页对象：包含 items（目标列表）、total（总条数）、page（当前页）、pages（总页数）
        """
        try:
            # 基础查询：关联用户表和分类表
            query = db.session.query(
                Goal,
                User.username,
                GoalCate.first_cate_name
            ).join(
                User, Goal.user_id == User.id, isouter=True
            ).join(
                GoalCate, Goal.first_cate_id == GoalCate.id, isouter=True
            )

            # 处理过滤条件
            if filters and isinstance(filters, dict):
                # 用户 ID 过滤
                if "user_id" in filters:
                    query = query.filter(Goal.user_id == filters["user_id"])
                # 分类 ID 过滤
                if "first_cate_id" in filters:
                    query = query.filter(Goal.first_cate_id == filters["first_cate_id"])
                # 目标状态过滤
                if "goal_status" in filters:
                    query = query.filter(Goal.goal_status == filters["goal_status"])
                # 目标名称模糊过滤
                if "goal_name" in filters and filters["goal_name"]:
                    query = query.filter(Goal.goal_name.like(f"%{filters['goal_name']}%"))
                # 用户名模糊过滤（关联表字段）
                if "username" in filters and filters["username"]:
                    query = query.filter(User.username.like(f"%{filters['username']}%"))

                # 开始日期范围过滤
                if "start_date_start" in filters and filters["start_date_start"]:
                    start_date = filters["start_date_start"]
                    if isinstance(start_date, str):
                        start_date = date.fromisoformat(start_date)
                    query = query.filter(Goal.start_date >= start_date)
                if "start_date_end" in filters and filters["start_date_end"]:
                    end_date = filters["start_date_end"]
                    if isinstance(end_date, str):
                        end_date = date.fromisoformat(end_date)
                    query = query.filter(Goal.start_date <= end_date)

                # 结束日期范围过滤
                if "end_date_start" in filters and filters["end_date_start"]:
                    start_date = filters["end_date_start"]
                    if isinstance(start_date, str):
                        start_date = date.fromisoformat(start_date)
                    query = query.filter(Goal.end_date >= start_date)
                if "end_date_end" in filters and filters["end_date_end"]:
                    end_date = filters["end_date_end"]
                    if isinstance(end_date, str):
                        end_date = date.fromisoformat(end_date)
                    query = query.filter(Goal.end_date <= end_date)

            # 分页查询（按 ID 降序，最新目标在前）
            pagination = query.order_by(Goal.id.desc()).paginate(
                page=page, per_page=per_page, error_out=False
            )

            # 格式化分页结果
            result_items = []
            for goal, username, first_cate_name in pagination.items:
                result_items.append({
                    "id": goal.id,
                    "user_id": goal.user_id,
                    "username": username or "未分配用户",
                    "first_cate_id": goal.first_cate_id,
                    "first_cate_name": first_cate_name or "未知分类",
                    "goal_name": goal.goal_name,
                    "start_date": goal.start_date.strftime("%Y-%m-%d") if goal.start_date else None,
                    "end_date": goal.end_date.strftime("%Y-%m-%d") if goal.end_date else None,
                    "goal_status": goal.goal_status,
                    "goal_status_desc": GoalDao._get_goal_status_desc(goal.goal_status)
                })

            # 返回分页信息（兼容前端分页组件）
            return {
                "items": result_items,
                "total": pagination.total,
                "page": page,
                "per_page": per_page,
                "pages": pagination.pages
            }
        except Exception as e:
            raise Exception(f"查询目标列表失败：{str(e)}")

    @staticmethod
    def _get_goal_status_desc(status):
        """
        目标状态编码转描述（辅助方法）
        :param status: 状态编码（0-未完成，1-完成，2-暂缓）
        :return: 状态描述字符串
        """
        status_map = {0: "未完成", 1: "完成", 2: "暂缓"}
        return status_map.get(status, "未知状态")
